# initialize a sqlite database and create a table
import os
import sqlite3

db_name = 'yarn.db'

def create_database_if_not_exists():
    # if database file exists, skip the creation
    if os.path.exists(db_name):
        return
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS videos
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    path TEXT,
                    title TEXT,
                    description TEXT,
                    duration TEXT,
                    size TEXT,
                    fps TEXT,
                    bitrate TEXT,
                    width TEXT,
                    height TEXT,
                    codec TEXT,
                    audio TEXT,
                    audio_channels TEXT,
                    audio_bitrate TEXT,
                    audio_sample_rate TEXT,
                    transcript TEXT,
                    download_url TEXT,
                    status TEXT)''')

    c.execute('''CREATE TABLE IF NOT EXISTS projects
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT,
                    description TEXT,
                    status TEXT,
                    created_at TEXT,
                    updated_at TEXT)''')
    conn.commit()


# create_database_if_not_exists()

def insert_video(video):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    # if already exists the video with the same path, skip the insertion
    c.execute('SELECT * FROM videos WHERE path=?', (video['path'],))
    if c.fetchone():
        return False

    c.execute('''INSERT INTO videos (path, title, description, duration, size, fps, bitrate, width, height, codec, audio, audio_channels, audio_bitrate, audio_sample_rate, transcript, download_url, status)
                    VALUES (?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?,
                    ?)''', (
        video['path'], video['title'], video['description'], video['duration'], video['size'], video['fps'],
        video['bitrate'], video['width'], video['height'], video['codec'], video['audio'], video['audio_channels'],
        video['audio_bitrate'], video['audio_sample_rate'], video['transcript'], video['download_url'],
        video['status']))
    conn.commit()
    return True


def get_video_by_path(path):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('SELECT * FROM videos WHERE path=?', (path,))
    return c.fetchone()


def get_all_videos():
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('SELECT * FROM videos')
    return c.fetchall()


def update_video_status(path, status):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('UPDATE videos SET status=? WHERE path=?', (status, path))
    conn.commit()


def delete_video_by_path(path):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('DELETE FROM videos WHERE path=?', (path,))
    conn.commit()


def get_videos_by_status(status):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('SELECT * FROM videos WHERE status=?', (status,))
    return c.fetchall()


def create_project(project):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    c.execute('''INSERT INTO projects (name, description, status, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?)''', (project['name'], project['description'], project['status'], project['created_at'], project['updated_at']))
    conn.commit()
    return True